#Submit to svn
# compare *.resx to the excel, add the new translated items from excel and list the diff items to the 'diff' columns

from BeautifulSoup import BeautifulSoup #HTML
#from BeautifulSoup import BeautifulStoneSoup #XML
#from xgoogle.translate import Translator

import types
import os
import re
import xlrd
import xlwt
import sys


OUT_RES = 'diff_txt_'
in_excel_file = 'Diff Xera Languages 3.0-12-12-2012.xls' #This standard excel file comes from Christie
OUT_EXCEL = 'Diff Xera Languages.xls'

EXCEL_START_ROW = 2
languages = ['en', 'es', 'fr', 'de']

def get_resx_file(lang):
    if lang == 'en':
        return 'Resources.resx'
    else:
        return 'Resources.' + lang + '.resx'

def html_encode(val):
    return val.replace('&','&amp;').replace('>', '&gt;').replace('<', '&lt;')

def html_decode(val):
    return val.replace('&amp;','&').replace('&gt;','>').replace('&lt;','<')

class ExcelLoader():
    def __init__(self, fname):
        self.wb = xlrd.open_workbook(fname)
        self.mastersheet = self.wb.sheet_by_index(0)
        self.newsheet = self.wb.sheet_by_index(1)
        
        self.HEAD_ROW = 1
        self.START_ROW = 2
        #self.master_items
        self.master_heads, self.master_contents = self.load(self.mastersheet)
        self.col_names = self.get_col_names()
        
    def get_col_names(self):
        diff_col = []
        for lang in languages:
            diff_col.append(lang + '-diff')
        return ['ID'] + languages + diff_col
                
    def load(self, sheet):
        heads = sheet.row_values(self.HEAD_ROW)
        contents = []
        
        for i in range(self.START_ROW, sheet.nrows):
            row = sheet.row_values(i)
            contents.append([html_decode(cell.strip()) for cell in row])
            
        return heads, contents
    
    def get_col_index(self, col_name):
        return self.col_names.index(col_name)
    
    '''Get row contents in excel by query string id
    '''
    def find_row(self, id):
        contents = self.master_contents
        for i in range(0, len(contents)):
            if id == contents[i][0].strip():
                return contents[i]
        return False
    
    def find_row_num(self, sheet, id):
        for row in range(self.START_ROW, sheet.nrows):
            if id == sheet.cell( row, 0 ).value.strip():
                return row
        return False
"""
    def get_master_items(self):
        '''
        list = []
        for row in range(self.START_ROW, sheet.nrows):
            if id == sheet.cell( row, 0 ).value.strip():
                return row
        '''
        return False
    


    def is_diff(self, sheet, row, diff_col ):
        if sheet.cell( row, diff_col ).value.strip():
            return False
        else:
            return row

    def get_val(self, sheet, row, col):
        return sheet.cell(row, col).value.strip()

    def get_new_items(self, col):
        list = []
        for i in range(self.START_ROW, self.new_sheet.nrows):
            row_values = self.new_sheet.row_values(i)
            id = row_values[0].strip()
            val = row_values[col].strip()
            list.append({'id':id, 'val':val})
        return list

    '''
    def get_diff_items(self, target_col, diff_col):
        list = []
        for row in range(self.START_ROW, sheet.nrows):
            row_values = self.sheet.row_values(row)
            if row_values[diff_col].strip():
                list.append({'id': row_values[0], 'val':row_values[target_col]})
        return list
    '''
"""


class Resx():
    def __init__(self, fname):
        self.resx = open(fname)
        self.ids , self.vals = self.init_xml()

    def init_xml(self):
        res_doc = self.resx.read()
        soup = BeautifulSoup(''.join(res_doc))
        datas = soup.findAll(['data'])
        data_names = [ data_tag['name'].strip() for data_tag in datas]#.encode('utf-8', 'ignore')
        contents = [ html_decode(data_tag.contents[1].contents[0]) for data_tag in datas]#.encode('utf-8', 'ignore')
        return (data_names, contents)

    def close(self):
        self.resx.close()
    '''Find string in res by query string id
    '''
    
    def find_row(self, id):
        for row in range(len(self.ids)):
            if id.strip() == self.ids[row]:
                return row
        return False
    
    def find_item(self, id):
        for i in range(len(self.ids)):
            if id.strip() == self.ids[i]:
                return self.vals[i]
        return False
    
    def html_encode(self, val):
        return val.replace('&','&amp;').replace('>', '&gt;').replace('<', '&lt;')

    def merge_to_new(self, fname, excel, target_col):
        self.fdiff = open(fname, 'w')
        buf = ''
        
        for row in range(excel.START_ROW, excel.master_sheet.nrows):
            id = excel.get_val(excel.master_sheet, row, 0).encode('utf-8').strip()
            new_translation = excel.get_val( excel.master_sheet, row, target_col ).encode('utf-8').strip()
            if id != "":
                #if new_translation != "":
                buf += '  <data name="' + id + '" xml:space="preserve">\n' + '    <value>' + self.html_encode(new_translation) + '</value>\n  </data>\n'
                #else:
                #    buf += '  <data name="' + id + '" xml:space="preserve">\n' + '    <value>' + "Pending Wording" + '</value>\n  </data>\n'
        
        new_items = excel.get_new_items(target_col)
        for i in range(len(new_items)):
            new_id = new_items[i]['id'].encode('utf-8').strip()
            if (type( new_id ) is not float) and (new_id != ""):
                new_translation = new_items[i]['val'].encode('utf-8').strip()
                buf += '  <data name="' + new_id + '" xml:space="preserve">\n' + '    <value>' + self.html_encode(new_translation) + '</value>\n  </data>\n'  

        self.fdiff.write(buf)        
        self.fdiff.close()


class ResourceMerger:
    def __init__(self, excel, resxs, fout):
        self.fout = fout
        self.wb = xlwt.Workbook(encoding="UTF-8")
        self.mastersheet = self.wb.add_sheet('Master')
        self.newsheet = self.wb.add_sheet('new items')
        self.encoding = 'utf-8'
        self.n_new_items = 0
        
        self.excel = excel
        self.HEAD_ROW = excel.HEAD_ROW
        self.START_ROW = excel.START_ROW
        self.N_LANGUAGES = len(languages)
        
        self.resxs = resxs

        self.get_diffs_from_resxs()
        #self.copy_excel(self.mastersheet,'Master')
        #self.save()
    
    def copy_excel_heads(self, sheet, sheet_name):
        if sheet_name == 'Master':
            heads = self.excel.master_heads
            for i in range(0, len(heads)):
                sheet.write(self.HEAD_ROW, i, heads[i])
        else:
            for i in range(0, self.N_LANGUAGES+1):
                sheet.write(self.HEAD_ROW, i, heads[i])

    # copy from excel to new excel, do html encoding for the contents
    def copy_excel(self, sheet, sheet_name):
        self.copy_excel_heads(sheet, sheet_name);
        if sheet_name == 'Master':
            master_contents = self.excel.master_contents
            
            for i in range(0, len(master_contents)):
                for j in range(0, self.N_LANGUAGES+1):
                    value = master_contents[i][j]
                    if type(value) is str:
                        value = value.encode(self.encoding)
                    self.mastersheet.write(i+self.START_ROW, j, value)
                       
    def save(self):
        self.wb.save(self.fout)
    
    def get_diff_from_resx(self, lang='en'):
        resx = self.resxs[lang]
        excel_contents = self.excel.master_contents
        resx_contents = resx.vals
        col = self.excel.get_col_index(lang)
        diffs = []
        
        for i in range(0, len(excel_contents)):
            id = excel_contents[i][0].strip()
            resx_val = resx.find_item(id)
            if resx_val:
                excel_val = excel_contents[i][col].strip()
                
                if excel_val != resx_val:
                    #self.w_master_sheet.write(i, col_num + LANG_COL_DIFF_LEN, 'Pending Wording')
                    diff = {'id': id, 'excel': excel_val, 'resx': resx_val}
                    print 'id:' + id + '      excel:'+ excel_val + '       resx:' + resx_val
                    diffs.append(diff)
        return diffs


    def get_diffs_from_resxs(self):
        diffs = {}
        for lang in languages:
            diffs[lang] = self.get_diff_from_resx(lang)
        return diffs
        
        
    # get new English items
    def get_new_from_resx(self, lang='en'):
        excel_contents = self.excel.master_contents
        resx_ids = self.resxs[lang].ids
        resx_contents = self.resxs[lang].vals
        col = self.excel.get_col_index(lang)
        news = []
        
        for i in range(0, len(resx_ids)):
            id = resx_ids[i]
            excel_row = self.excel.find_row(id)
            if not excel_row: #If can't find the item in excel
                #self.w_master_sheet.write(i, col_num + LANG_COL_DIFF_LEN, 'Pending Wording')
                new = {'id': id, 'new': resx_contents[i] }
                print 'id:' + id + '      excel:'+ resx_contents[i]
                news.append(new)
        return news
    
    def copy_new_from_resx(self, news, lang):
        resx = self.resxs[lang]
        for i in range(0, len(news)):
            ret = resx.find_item(news[i]['id'])
            if ret != False:
                col_num = self.excel.get_col_index(lang)
                if lang == 'en':
                    self.newsheet.write(i+self.START_ROW, col_num, news[i]['new'][lang])
            else:
                col_num = self.excel.get_col_index(lang)
                self.newsheet.write(i+self.START_ROW, col_num, ret[i])
                
    def copy_news_from_resx(self, news):
        for lang in languages:
            self.copy_new_from_resx(news, lang)
                
    def copy_diff_from_resx(self, lang='en'):
        
        LANG_COL_DIFF_LEN = self.N_LANGUAGES
        master_contents = self.excel.master_contents
        
        for i in range(0, len(master_contents)):
            col_num = self.excel.get_col_index(lang)
            
            id = master_contents[i][0]
            
            ret = self.resxs[lang].find_item(id)
            if ret != False:
                excel_val = master_contents[i][col_num]
                
                if excel_val == '':
                    self.mastersheet.write(i + self.START_ROW, col_num + LANG_COL_DIFF_LEN, 'Pending Wording')
                else:
                    resx_val = ret
                    if excel_val != resx_val:
                        self.mastersheet.write(i + self.START_ROW, col_num + LANG_COL_DIFF_LEN, resx_val)
                        
    def copy_diffs_from_resx(self):
        for lang in languages:
            self.copy_diff_from_resx(lang)
            

'''        
class ExcelMerge:
    def __init__(self, out_excel):
        self.rwb = xlrd.open_workbook(in_excel_file)
        self.rsheet = self.rwb.sheet_by_index(0)

        self.wwb = xlwt.Workbook(encoding="UTF-8")
        self.w_master_sheet = self.wwb.add_sheet('Master')
        self.w_new_sheet = self.wwb.add_sheet('new items')
        self.encoding = 'utf-8'
        self.n_new_items = 0

        self.result_excel = out_excel
        #self.spanish_res = init_xml_res()
        #self.german_res = init_xml_res(GERMAN_RES_FILE)        

    def copy_execel(self):
        self.write_headers(self.w_master_sheet, 'Master');
        for i in range(EXCEL_START_ROW, self.rsheet.nrows):
            for j in range(0, N_LANGUAGES+1):
                rcontent = self.rsheet.cell( i, j ).value
                if type(rcontent) is str:
                    rcontent = rcontent.encode(self.encoding)
                self.w_master_sheet.write(i,j, rcontent)

    def find_item_in_excel(self, item_id, sheet):
        for row in range(EXCEL_START_ROW, sheet.nrows):
            if item_id.strip().encode(self.encoding) == sheet.cell( row, 0 ).value.strip():
                return row
        return False

    def find_item_in_res(self, item_id, ids):
        for row in range(0, len(ids)):
            if item_id.strip() == ids[row].strip():
                return row
        return False
    
    def get_col_index(self,id):
        return col_ids.index(id)

    def copy_new_translation_from_res(self, res_file, new_ids, lang_id):
        res = self.init_xml_res(res_file)
        for i in range(0, self.n_new_items):
            ret = self.find_item_in_res(new_ids[i], res[0])
            if ret != False:
                col_num = self.get_col_index(lang_id)
                self.w_new_sheet.write(i+EXCEL_START_ROW, col_num, res[1][ret])

    def write_headers(self, sheet, sheet_name):
        if sheet_name == 'Master':
            for i in range(0, len(col_ids)):
                sheet.write(EXCEL_START_ROW-1, i, col_ids[i])
        else:
            for i in range(0, N_LANGUAGES+1):
                sheet.write(EXCEL_START_ROW-1, i, col_ids[i])
                
    def copy_new_items_from_res(self):
        # Get new ids and contents from the english
        self.write_headers(self.w_new_sheet, 'New items');
        res = self.init_xml_res(ENGLISH_RES_FILE)
        new_ids = []
        for i in range(0, len(res[0])):
            item_id = res[0][i]
            english = res[1][i]
            ret = self.find_item_in_excel( item_id, self.rsheet )
            if ret == False:
                self.w_new_sheet.write(self.n_new_items+EXCEL_START_ROW, 0, item_id)
                self.w_new_sheet.write(self.n_new_items+EXCEL_START_ROW, 1, english)
                new_ids.append(item_id)
                self.n_new_items += 1
        
        self.copy_new_translation_from_res(SPANISH_RES_FILE,new_ids,'Spanish')
        self.copy_new_translation_from_res(FRENCH_RES_FILE,new_ids,'French')
        self.copy_new_translation_from_res(GERMAN_RES_FILE,new_ids,'German')        

    def copy_diff_translation_from_res(self, res_file, lang_id):
        res = self.init_xml_res(res_file)

        diff_txt = open(OUT_RES + lang_id,'w')
        for item in res[1]:
            diff_txt.write("%s\n" % item)
        diff_txt.close()

        LANG_COL_DIFF_LEN = self.get_col_index('English diff') - self.get_col_index('English')
        for i in range(EXCEL_START_ROW, self.rsheet.nrows):
            col_num = self.get_col_index(lang_id)
            id = self.rsheet.cell( i, 0 ).value.strip()
            ret = self.find_item_in_res(id, res[0])
            if ret != False:
                rcontent = self.rsheet.cell( i, col_num ).value.strip()
                if rcontent == '':
                    self.w_master_sheet.write(i, col_num + LANG_COL_DIFF_LEN, 'Pending Wording')
                else:
                    res_content = (res[1][ret]).strip()
                    if rcontent.encode(self.encoding) != res_content:
                        self.w_master_sheet.write(i, col_num + LANG_COL_DIFF_LEN, res_content)

    def copy_diff_items_from_res(self):
        self.copy_diff_translation_from_res(ENGLISH_RES_FILE,'English')
        self.copy_diff_translation_from_res(SPANISH_RES_FILE,'Spanish')
        self.copy_diff_translation_from_res(FRENCH_RES_FILE,'French')
        self.copy_diff_translation_from_res(GERMAN_RES_FILE,'German')
        
    def save(self):
        self.wwb.save(self.out_excel)
        #self.rwb.close()
        
    def append_row( self, row ):
        return 0;
        
    # analyse empty items from the result diff file, and write to empty items sheet
    def gen_empty_items(self):
        # Open master sheet
        workbook = xlrd.open_workbook(self.out_excel)
        master_sheet = workbook.sheet_by_index(0)
'''
if __name__ == "__main__":
    #excelMerge = ExcelMerge()
    #myarray = excelMerge.init_xml_res(SPANISH_RES_FILE)
    #kk = excelMerge.find_item_in_res('StrAllowBatches', myarray[0]);
    #print myarray[1][kk]
    '''
    excelMerge = ExcelMerge(OUT_EXCEL)
    excelMerge.copy_execel()
    excelMerge.copy_new_items_from_res()
    excelMerge.copy_diff_items_from_res()
    excelMerge.save()
    '''
    #process_file(in_res_file, in_excel_file, out_res_file)
    excel = ExcelLoader(in_excel_file)
    resxs = {}
    
    for lang in languages:
        resxs[lang] = (Resx(get_resx_file(lang)))

    res_merger = ResourceMerger(excel, resxs, OUT_EXCEL)
    #res_merger.get_diff_from_resx()
    #res_merger.get_diffs_from_resxs()
    
    #res_merger.get_new_from_resx()
    res_merger.copy_excel(res_merger.mastersheet,'Master')
    res_merger.copy_diffs_from_resx()
    res_merger.copy_news_from_resx(res_merger.get_new_from_resx())
    res_merger.save()